package server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DbConnector {

	/**
	 * Database class is used as a directly access to the database through the
	 * defined methods
	 * 
	 * @author XA9
	 */
	private static DbConnector unique = null;

	private static String sqlUrl = "jdbc:mysql://localhost:3306/BCBSDatabase";
	private static String sqlUser = "root";
	private static String sqlPasswd = "";
	private static String sqlDriver = "com.mysql.jdbc.Driver";

	/**
	 * sets the static variables which are needed to work with the mysql
	 * database
	 * 
	 * @param _sqlUrl
	 *            ip of the server or ('localhost' / 172.17.132.235)
	 * @param _sqlPort
	 *            the sql connector port - defaults to 3306
	 * @param _sqlUser
	 *            the sql user to connect with - default admin is root
	 * @param _sqlPasswd
	 *            password to the sql user
	 * @param db
	 *            the database to work with (null or "" if no default database
	 *            is needed)
	 */
	
	
	public static void setDatabaseInfo(String _sqlUrl, String _sqlPort,
			String _sqlUser, String _sqlPasswd, String db) {
		sqlUrl = "jdbc:mysql://" + _sqlUrl + ":" + _sqlPort + "/";
		sqlUser = _sqlUser;
		sqlPasswd = _sqlPasswd;

		if (db != null && db.length() > 0)
			sqlUrl += db;
	}

	/**
	 * private constructor makes sure that no other class can make objects of
	 * class DbConnector
	 * 
	 * an instance of the class can only generate by calling getInstance()
	 */

	private DbConnector() {
		try {
			Class.forName(sqlDriver).newInstance();
		}

		catch (Exception ex) {
			System.out.println(ex);
		}
	}

	/**
	 * Returns object of DbConnector
	 * 
	 * @return unique
	 */
	public static DbConnector getInstance() {
		if (unique == null)
			unique = new DbConnector();

		return unique;
	}

	/**
	 * Sends an update to the database
	 * 
	 * @param update
	 *            the sql statement to execute
	 */
	public void doUpdate(String update) throws SQLException {
		DatabaseConn db = new DatabaseConn();

		db.doUpdate(update);

	}

	/**
	 * sends a request to the database
	 * 
	 * @param query
	 *            sql script to execute
	 * @return a ResultSet object that contains the data produced by the given
	 *         query; never null
	 */
	public ResultSet doQuery(String query) throws SQLException {
		DatabaseConn db = new DatabaseConn();

		return db.doQuery(query);
	}

	/**
	 * Test if a connection to a database is valid.
	 * 
	 * @return "Successfully connected" if valid. Else the return string will be
	 *         an error msg.
	 */
	public String testConnection() {
		DatabaseConn db = new DatabaseConn();
		String result = db.testConnection();
		return result;
	}

	/**
	 * Inner class that implements a connection to DB with query / update
	 * methods
	 * 
	 * Returns the result
	 * 
	 * @return result
	 * 
	 */
	private class DatabaseConn {

		private Statement stmt;
		private ResultSet rs;
		private Connection conn = null;

		public String testConnection() {

			String result = "Succesfully connected";

			try {
				conn = DriverManager.getConnection(sqlUrl, sqlUser, sqlPasswd);
				if (conn.isValid(5000))
					return result;
			} catch (SQLException e) {

				result = e.getMessage();

			}

			return result;
		}

		/**
		 * sends a request to the database
		 * 
		 * @param query
		 *            sql script to execute
		 * @return a ResultSet object that contains the data produced by the
		 *         given query; never null
		 */
		public ResultSet doQuery(String query) throws SQLException {

			conn = DriverManager.getConnection(sqlUrl, sqlUser, sqlPasswd);
			try {
				stmt = conn.createStatement();
				rs = stmt.executeQuery(query);
			}

			catch (SQLException ex) {
				System.out.println(ex);
				throw ex;
			}

			return rs;
		}

		/**
		 * Sends an update to the database
		 * 
		 * @param update
		 *            the sql statement to execute
		 * @return either (1) the row count for SQL Data Manipulation Language
		 *         (DML) statements or (2) 0 for SQL statements that return
		 *         nothing
		 */
		public int doUpdate(String update) throws SQLException {
			if (conn == null)
				conn = DriverManager.getConnection(sqlUrl, sqlUser, sqlPasswd);

			int temp = 0;

			try {
				stmt = conn.createStatement();
				temp = stmt.executeUpdate(update);
			}

			catch (SQLException ex) {
				ex.printStackTrace();
				if (ex.getErrorCode() == 1062)
					throw new SQLException("Duplicate entry");
				else
					throw ex;
			}

			finally {
				if (stmt != null) {
					try {
						stmt.close();
					}

					catch (SQLException sqlEx) {
						stmt = null;
					}
				}
			}

			return temp;
		}

	}
}
